/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package control;

import java.sql.ResultSet;
import java.sql.SQLException;
import model.Route;

/**
 *
 * @author Joseph
 */
public class GoodsPriceHandler {

    private DatabaseConnection db;

    public GoodsPriceHandler(DatabaseConnection db) {
        this.db = db;
    }

    public int getPriceByRoute(Route route, boolean dangerousGoods, int amount, int weight, int goods_type) throws SQLException, Exception {
        int price = 0;

        String sql = "SELECT max(maxWeight), max(maximum) FROM goods_price WHERE goods_type = " + goods_type;
        ResultSet rs = db.selectQuery(sql);
        int maxWeight = 0;
        int maximum = 0;
        if (rs.next()) {
            maxWeight = rs.getInt(1);
            maximum = rs.getInt(2);
        }
        rs.close();
        if (amount > maximum) {
            throw (new Exception("Fylder for meget."));
        } else if (weight > maxWeight) {
            throw (new Exception("Vejer for meget."));
        }

        sql = "SELECT ROUND(IF(" + dangerousGoods + ", price_per_km*" + route.getKm() + "*1.1, price_per_km*" + route.getKm() + "),0)"
                + " as price, price_per_km "
                + "FROM goods_price WHERE goods_type = " + goods_type + " AND (" + amount + " <= maximum AND " + weight + " <= maxWeight) "
                + "ORDER BY maximum ASC, maxWeight ASC LIMIT 1";
        rs = db.selectQuery(sql);
        if (rs.next()) {
            price = rs.getInt("price");
        }

        return price;
    }

}
